set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_arbit_trans_duty_summary_dt
select
     duty_main_code         --责任主体编码
    ,duty_main_name         --责任主体名称
    ,duty_main_agent_code   --责任代理区编码
    ,duty_main_agent_name   --责任代理区名称
    ,count(1) as delay_cnt
    ,count(case when delay_day = 1 then waybill_no end) as delay_day1_cnt
    ,count(case when delay_day = 2 then waybill_no end) as delay_day2_cnt
    ,count(case when delay_day = 3 then waybill_no end) as delay_day3_cnt
    ,count(case when delay_day = 4 then waybill_no end) as delay_day4_cnt
    ,count(case when delay_day = 5 then waybill_no end) as delay_day5_cnt
    ,count(case when delay_day = 6 then waybill_no end) as delay_day6_cnt
    ,count(case when delay_day = 7 then waybill_no end) as delay_day7_cnt
    ,count(case when delay_day > 7 then waybill_no end) as delay_day7_plus_cnt
    ,port_type
    ,count(case when center_type = 1 then waybill_no end) as start_center_delay_cnt
    ,count(case when center_type = 2 then waybill_no end) as trans_center_delay_cnt
    ,count(case when center_type = 3 then waybill_no end) as end_center_delay_cnt
    ,dt
from jms_dm.dm_arbit_trans_duty_detail_dt
where dt = '{{ execution_date | cst_ds }}'
group by duty_main_code                   --责任主体编码
        ,duty_main_name                   --责任主体名称
        ,duty_main_type                   --责任主体类型
        ,duty_main_agent_code             --责任代理区编码
        ,duty_main_agent_name             --责任代理区名称
        ,port_type
        ,dt
distribute by pmod(hash(rand()),1);
